﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace GroupProject
{
    public partial class frmEmployees : Form
    {
        SqlConnection con;
        SqlDataAdapter da;
        DataSet ds;
        SqlCommandBuilder cb;
        DataTable dt;
        DataView dv;
        SqlCommand cmd;

        public frmEmployees()
        {
            InitializeComponent();
            InitializeADOObject();
        }

        void InitializeADOObject()
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
            try
            {
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "getEmployees";
                da = new SqlDataAdapter(cmd);
                ds = new DataSet();
                da.FillSchema(ds, SchemaType.Mapped);
                da.Fill(ds, "Employees");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        void loadEmployeeInfo()
        {
            dt = ds.Tables["Employees"];
            dv = new DataView(dt);
            dataGridView1.DataSource = dt;
        }

        private void frmEmployees_Load(object sender, EventArgs e)
        {
            loadEmployeeInfo();
        }

        void addNewEmployee()
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
            try
            {
                con.Open();
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "insertEmployee";
                SqlParameter param = new SqlParameter("@lastname", SqlDbType.NVarChar);
                param.Size = 20;
                param.Value = txtLastname.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@firstname", SqlDbType.NVarChar);
                param.Size = 10;
                param.Value = txtFirstname.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@title", SqlDbType.NVarChar);
                param.Size = 30;
                param.Value = txtTitle.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@titleofcourtesy", SqlDbType.NVarChar);
                param.Size = 25;
                param.Value = cbTOC.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@birthdate", SqlDbType.DateTime);
                param.Value = dTPBD.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@hiredate", SqlDbType.DateTime);
                param.Value = dTPHD.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@address", SqlDbType.NVarChar);
                param.Size = 60;
                param.Value = txtAddress.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@city", SqlDbType.NVarChar);
                param.Size = 15;
                param.Value = cbCity.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@region", SqlDbType.NVarChar);
                param.Size = 15;
                param.Value = cbRegion.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@postalcode", SqlDbType.NVarChar);
                param.Size = 10;
                param.Value = txtPostalCode.Text.Trim();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@country", SqlDbType.NVarChar);
                param.Size = 15;
                param.Value = cbCountry.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@phone", SqlDbType.NVarChar);
                param.Size = 24;
                param.Value = mtxtPhone.Text.Trim();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@mgrid", SqlDbType.Int);
                param.Size = 2;
                if (txtMgrid.Text.Length == 0)
                    param.Value = DBNull.Value;
                else
                    param.Value = txtMgrid.Text.Trim();
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            if (validateInput() == false) return;
                addNewEmployee();
            InitializeADOObject();
            loadEmployeeInfo();
            errorProvider1.Clear();
        }

        bool validateInput()
        {
            bool error = false;

            string lastname = txtLastname.Text;
            if (lastname.Length == 0)
            {
                errorProvider1.SetError(txtLastname,
                    "Please enter the last name!");
                error = true;
            }
            
            string firstname = txtFirstname.Text;
            if (firstname.Length == 0)
            {
                errorProvider1.SetError(txtFirstname,
                    "Please enter the first name!");
                error = true;
            }

            string title = txtTitle.Text;
            if (title.Length == 0)
            {
                errorProvider1.SetError(txtTitle,
                    "Please enter the title!");
                error = true;
            }

            string titleOC = cbTOC.Text;
            if (titleOC.Length == 0)
            {
                errorProvider1.SetError(cbTOC,
                    "Please choice the title of courtesy!");
                error = true;
            }

            DateTime currDate = DateTime.Now;
            int currYear = currDate.Year;
            DateTime birthdate = dTPBD.Value;
            int birthYear = birthdate.Year;
            if (currYear - birthYear < 18)
            {
                errorProvider1.SetError(dTPBD,
                    "Employee's age must be equal or greater than 18!");
                error = true;
            }

            DateTime hiredate = dTPHD.Value;
            int hireYear = hiredate.Year;
            if (hireYear - birthYear < 18)
            {
                errorProvider1.SetError(dTPHD,
                    "Employee's age must be 18+ to be hired!");
                error = true;
            }

            string posCode = txtPostalCode.Text;
            bool isNum = false;
            int newCode;
            isNum = Int32.TryParse(posCode, out newCode);
            if (!isNum)
            {
                errorProvider1.SetError(txtPostalCode,
                    "Postal Code must be number!");
                error = true;
            }
            else
            {
                if (Int32.Parse(posCode.Trim()) < 0 || txtPostalCode.Text.Trim().Length != 5)
                {
                    errorProvider1.SetError(txtPostalCode,
                    "Postal Code must include 5 positive digits!");
                error = true;
                }
            }

            int pars;
            bool parse=int.TryParse(txtMgrid.Text.Trim(),out pars);
            if (parse==false)
            {
                errorProvider1.SetError(txtMgrid,"Manager ID must be number");
                error = true;
            }
            else
            {
                pars=int.Parse(txtMgrid.Text.Trim());
                if (pars<0)
                {
                    errorProvider1.SetError(txtMgrid, "Manager ID must be >0");
                    error = true;
                }
            }
            if (error == true) return false;
            else errorProvider1.Clear();
            
            return true;
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            btnAdd.Enabled = true;
            txtLastname.Clear();
            txtFirstname.Clear();
            txtTitle.Clear();
            cbTOC.SelectedIndex = -1;
            dTPBD.Value = DateTime.Now;
            dTPHD.Value = DateTime.Now;
            txtAddress.Clear();
            cbCity.SelectedIndex = -1;
            cbRegion.SelectedIndex = -1;
            txtPostalCode.Clear();
            cbCountry.SelectedIndex = -1;
            mtxtPhone.Clear();
            txtMgrid.Clear();
            errorProvider1.Clear();
        }

        private void dataGridView1_SelectionChanged(object sender, EventArgs e)
        {
            if (dataGridView1.SelectedRows.Count > 0)
            {
                btnUpdate.Enabled = true;
                btnDelete.Enabled = true;
                btnAdd.Enabled = false;
                try
                {
                    DataGridViewRow r = dataGridView1.SelectedRows[0];
                    lbID.Text = r.Cells[0].Value.ToString();
                    txtLastname.Text = r.Cells[1].Value.ToString();
                    txtFirstname.Text = r.Cells[2].Value.ToString();
                    txtTitle.Text = r.Cells[3].Value.ToString();
                    cbTOC.Text = r.Cells[4].Value.ToString();
                    DateTime dt;
                    DateTime.TryParse(r.Cells[5].Value.ToString(), out dt);
                    dTPBD.Value = dt;
                    DateTime.TryParse(r.Cells[6].Value.ToString(), out dt);
                    dTPHD.Value = dt;
                    txtAddress.Text = r.Cells[7].Value.ToString();
                    cbCity.Text = r.Cells[8].Value.ToString();
                    cbRegion.Text = r.Cells[9].Value.ToString();
                    txtPostalCode.Text = r.Cells[10].Value.ToString();
                    cbCountry.Text = r.Cells[11].Value.ToString();
                    mtxtPhone.Text = r.Cells[12].Value.ToString();
                    txtMgrid.Text = r.Cells[13].Value.ToString();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }
        void updateEmployee()
        {
            if (dataGridView1.SelectedRows.Count > 0)
            {
                con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
                try
                {
                    con.Open();
                    cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "updateEmployee";
                    SqlParameter param = new SqlParameter("@empid", SqlDbType.Int);
                    param.Value = lbID.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@lastname", SqlDbType.NVarChar);
                    param.Size = 20;
                    param.Value = txtLastname.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@firstname", SqlDbType.NVarChar);
                    param.Size = 10;
                    param.Value = txtFirstname.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@title", SqlDbType.NVarChar);
                    param.Size = 30;
                    param.Value = txtTitle.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@titleofcourtesy", SqlDbType.NVarChar);
                    param.Size = 25;
                    param.Value = cbTOC.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@birthdate", SqlDbType.DateTime);
                    param.Value = dTPBD.Value.ToShortDateString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@hiredate", SqlDbType.DateTime);
                    param.Value = dTPHD.Value.ToShortDateString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@address", SqlDbType.NVarChar);
                    param.Size = 60;
                    param.Value = txtAddress.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@city", SqlDbType.NVarChar);
                    param.Size = 15;
                    param.Value = cbCity.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@region", SqlDbType.NVarChar);
                    param.Size = 15;
                    param.Value = cbRegion.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@postalcode", SqlDbType.NVarChar);
                    param.Size = 10;
                    param.Value = txtPostalCode.Text.Trim();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@country", SqlDbType.NVarChar);
                    param.Size = 15;
                    param.Value = cbCountry.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@phone", SqlDbType.NVarChar);
                    param.Size = 24;
                    param.Value = mtxtPhone.Text.Trim();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@mgrid", SqlDbType.Int);
                    param.Size = 2;
                    if (txtMgrid.Text.Length == 0)
                        param.Value = DBNull.Value;
                    else
                        param.Value = txtMgrid.Text.Trim();
                    cmd.Parameters.Add(param);

                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    con.Close();
                }
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            if (!validateInput()) return;
            updateEmployee();
            InitializeADOObject();
            loadEmployeeInfo();
        }

        void deleteEmployee()
        {
            if (dataGridView1.SelectedRows.Count > 0)
            {
                    con = new SqlConnection();
                    con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
                try
                {
                    con.Open();
                    cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "deleteEmployee";
                    SqlParameter param = new SqlParameter("@empid", SqlDbType.Int);
                    param.Value = lbID.Text;
                    cmd.Parameters.Add(param);

                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Deleting completed!");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    con.Close();
                }
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            DialogResult rs = MessageBox.Show("Are you sure to delete?",
                "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (rs == DialogResult.Yes)
            {
                deleteEmployee();
                InitializeADOObject();
                loadEmployeeInfo();
            }
        }

        void SearchByName()
        {
            dv = new DataView(dt);
            dv.RowFilter = string.Format("lastname +' '+ firstname like '%{0}%'", txtSearch.Text);
            dataGridView1.DataSource = dv;
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            SearchByName();
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            InitializeADOObject();
            loadEmployeeInfo();
        }

        private void frmEmployees_FormClosing(object sender, FormClosingEventArgs e)
        {
            DialogResult result;
            result = MessageBox.Show("Are you sure to exit?", "Confirm", MessageBoxButtons.YesNo);
            if (result == DialogResult.No)
                e.Cancel = true;
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

    }
}
